package com.common.utils.generatedocumentation;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.common.utils.db.DB;
import com.common.utils.db.Utils;
import com.common.utils.excel.GenerateExcel;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * @Author: zengliang
 * @Description: 一键自动生成数据库数据字典V1.0
 * @Date:Created in 14:02 2022/4/11
 * @Modified By:
 * @Instructions:
 *  GenerateDataDictionary generateDataDictionary = new GenerateDataDictionary();
 *  generateDataDictionary.getDataDictionary();
 *  @result:
 *  D:\数据字典\xxxx.xml
 */
public class GenerateDataDictionary {



    /**
     * 生成Excel文档
     * @param map
     * @param fieldMap
     * @param filePath
     */
    public  void createWorkbook(Map<String,Object> map,Map<String,Object> fieldMap,String filePath){
        Utils utils = new Utils();
        String database = utils.getSystemConfigurationForname("database");

        GenerateExcel generateExcel = new GenerateExcel();
        //自动列宽
        Map<Integer,Integer> maxWidth = new HashMap<>(map.size());
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("表说明");
        Row row;
        Cell cell;
        Row row0 = sheet.createRow(0);
        cell = row0.createCell(0);
        cell.setCellValue("库名");
        //设置字体
        CellStyle cellStyle = generateExcel.createCellStyle(workbook);
        cell.setCellStyle(cellStyle);

        cell = row0.createCell(1);
        cell.setCellValue("库说明");
        cell.setCellStyle(cellStyle);

        int i = 0;
        for(String key : map.keySet()){
            CreationHelper createHelper = workbook.getCreationHelper();
            XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.DOCUMENT);
            // "#"表示本文档    "明细页面"表示sheet页名称  "A10"表示第几列第几行
            if(key.length() > 31){
                link.setAddress("#"+key.substring(0, 31)+"!A1");
            }else{
                link.setAddress("#"+key+"!A1");
            }
            if(i == 2){
                Row row1 = sheet.createRow(i);
                cell = row1.createCell(0);
                cell.setCellValue("表名");
                cell.setCellStyle(cellStyle);
                cell = row1.createCell(1);
                cell.setCellValue("表说明");
                cell.setCellStyle(cellStyle);
            }
            //设置超链接样式
            CellStyle linkStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setUnderline((byte) 1);
            font.setColor(IndexedColors.BLUE.getIndex());
            linkStyle.setFont(font);

            String value = map.get(key) +"";
            row = sheet.createRow(i + 1);
            Cell cell0 = row.createCell(0);
            cell0.setCellValue(key);
            cell0.setHyperlink(link);
            cell0.setCellStyle(linkStyle);

            Cell cell1 = row.createCell(1);
            cell1.setCellValue(value);
            maxWidth.put(i,key.getBytes().length * generateExcel.getWidthFactor() + 400);
            i++;
        }

        //创建表明细
        int j = 0;
        for (String key : fieldMap.keySet()) {
            Sheet tableSheet = workbook.createSheet(key);
            row = tableSheet.createRow(0);
            cell = row.createCell(0);
            cell.setCellValue("字段名");
            cell.setCellStyle(cellStyle);

            cell = row.createCell(1);
            cell.setCellValue("字段类型");
            cell.setCellStyle(cellStyle);

            cell = row.createCell(2);
            cell.setCellValue("字段说明");
            cell.setCellStyle(cellStyle);

            JSONArray jsonArray = JSONArray.parseArray(fieldMap.get(key) + "");

            for(int s =0;s<jsonArray.size();s++){
                JSONObject jsonObject = jsonArray.getJSONObject(s);
                row = tableSheet.createRow(s+1);
                cell = row.createCell(0);
                cell.setCellValue(jsonObject.get("column_name")+"");

                cell = row.createCell(1);
                cell.setCellValue(jsonObject.get("data_type")+"");

                cell = row.createCell(2);
                cell.setCellValue(jsonObject.get("column_comment")+"");
            }
            //自动适应列宽
            generateExcel.getAutoSizeColumn(tableSheet,4);
            j++;
        }
        //自动适应列宽
        generateExcel.getAutoSizeColumn(sheet,3);
        //生成Excel
        generateExcel.createExcel(workbook,filePath,""+database+".xlsx");
    }

    /**
     * 访问数据库获取表数据信息
     */
    public  void getDataDictionary(){
        Utils utils = new Utils();
        String database = utils.getSystemConfigurationForname("database");

        Connection conn = DB.getConn();
        Statement stmt = DB.getStmt(conn);
        ResultSet resultSet = null;
        ResultSet resultSetFields = null;
        try {
            String sql  ="SELECT\n" +
                    "\ttable_name,table_comment \n" +
                    "FROM\n" +
                    "\tINFORMATION_SCHEMA.TABLES \n" +
                    "WHERE\n" +
                    "\tTABLE_SCHEMA = '"+database+"' \n" +
                    "\tAND table_name NOT LIKE '%copy%' \n" +
                    "\tAND table_comment is not null\n" +
                    "\torder by table_name asc;";
            // PreparedStatement statmend = conn.prepareStatement(sql);
            resultSet = stmt.executeQuery(sql);
            LinkedHashMap<String,Object> tableMap = new LinkedHashMap<String,Object>();
            LinkedHashMap<String,Object> fieldMap = new LinkedHashMap<String,Object>();
            tableMap.put(database,"");
            while(resultSet.next()) {
                Statement stmt1 =DB.getStmt(conn);
                String tableName = resultSet.getString("table_name");
                String tableComment = resultSet.getString("table_comment");
                tableMap.put(tableName,tableComment);
                String sqlField = "\n" +
                        "SELECT DISTINCT\n" +
                        "\tcolumn_name ,\n" +
                        "\tdata_type,\n" +
                        "\tcolumn_comment \n" +
                        "FROM\n" +
                        "\tinformation_schema.COLUMNS \n" +
                        "WHERE\n" +
                        "\ttable_schema = '"+database+"' \n" +
                        "\tAND table_name = '"+tableName+"'";

                //表字段明细
                resultSetFields = stmt1.executeQuery(sqlField);
                List<JSONObject> list = new ArrayList<JSONObject>();
                while(resultSetFields.next()) {
                    JSONObject jsonObject = new JSONObject();
                    //字段名称
                    String column_name = resultSetFields.getString("column_name");
                    //字段类型
                    String data_type = resultSetFields.getString("data_type");
                    //字段备注
                    String column_comment = resultSetFields.getString("column_comment");
                    jsonObject.put("column_name",column_name);
                    jsonObject.put("data_type",data_type);
                    jsonObject.put("column_comment",column_comment);
                    list.add(jsonObject);
                }
                fieldMap.put(tableName,list);
            }
            DB.closeRs(resultSet);
            DB.closeConn(conn);
            String filePath  = "D:\\数据字典\\";
            GenerateDataDictionary generateDataDictionary = new GenerateDataDictionary();
            generateDataDictionary.createWorkbook(tableMap,fieldMap,filePath);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        GenerateDataDictionary generateDataDictionary = new GenerateDataDictionary();
        generateDataDictionary.getDataDictionary();
    }
}
